from sqlalchemy import create_engine
from sqlalchemy.orm import Session
from sqlalchemy.ext.automap import automap_base
import datetime
from config.url_conf import db_url

# 数据库连接，格式：mysql+pymysql://<username>:<password>@<host>/<dbname>[?<options>]
db_connect_string = db_url


class DB():
    def __init__(self, db_connect):
        # 创建引擎
        self.engine = create_engine(db_connect, echo=True)  # echo: 是否打印映射时的sql语句
        self.session = Session(self.engine)
        # 自动映射
        self.base = automap_base()
        self.base.prepare(self.engine, reflect=True)

    def table(self, table_name):
        self.name = self.base.classes.__getitem__(key=table_name)
        return self.name

    def query_repay_plan(self, borrowid, tablename):
        res = self.session.query(self.table(tablename)).filter_by(contract_id=borrowid, repay_status=3).first()
        return (datetime.datetime.strftime(res.repay_date, '%Y-%m-%d'), str(res.plan_amount), str(res.plan_principal),
                str(res.plan_interest), str(res.plan_period_service),
                res.current_period, res.contract_number)


db = DB(db_connect_string)

if __name__ == '__main__':
    result = db.query_repay_plan(borrowid='2792613', tablename='repay_plan')
    print(result)
